<!--
	作者：Sariay
	时间：2018-09-25
	描述：There may be a bug, but don't worry, QiLing(器灵) says that it can work normally!
-->
<!DOCTYPE html>
<html class="html-loading">
		

<head><meta name="generator" content="Hexo 3.8.0">
	<meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1.0, user-scalable=no">
  <title>
    
      mysql高级 | SorrisoYi
    
  </title>
  <meta name="author" content="SorrisoYi">
  <meta name="keywords" content>
  <meta name="description" content="blog">
	<!-- favicon -->
  <link rel="shortcut icon" href="https://addyzk.github.io/Random-img/favicon.ico">

  <!-- css -->
  <link rel="stylesheet" href="https://cdn.bootcss.com/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="../../../../css/Annie.css">
  
  <!-- jquery -->
	<script src="../../../../js/jquery.min.js"></script>

  <!-- leancloud -->
  <script src="https://cdn1.lncld.net/static/js/av-core-mini-0.6.4.js"></script>
<script src="../../../../js/leancloud.js"></script>
</head>
	<body>
		<!-- Preloader -->

	<div id="preloader">
		<div class="pre-container">
			
				<div class="spinner">
					<div class="double-bounce1"></div>
					<div class="double-bounce2"></div>
				</div>
						
		</div>
	</div>


<!-- header -->
<header class="fixbackground" data-img-mode data-normal-src="../../../../index.html" data-random-max data-random-src="../../../../index.html">
	<div class="mask">
		<!-- Logo and navigation -->
		<div class="h-header">
			<div id="logo">
				<a href="/">
						
						<img src="https://addyzk.github.io/Random-img/logo.png" alt="Logo">
					
				</a>
			</div>
			
			<div id="navigation-show">
				<ul>
	
		<li class="menu-home">
			<a href="../../../../index.html" class="menu-item-home">主页</a>
		</li>
	
		<li class="menu-archive">
			<a href="../../../.././archives" class="menu-item-archive">归档</a>
		</li>
	
		<li class="menu-categories">
			<a href="../../../.././categories" class="menu-item-categories">分类</a>
		</li>
	
		<li class="menu-tags">
			<a href="../../../.././tags" class="menu-item-tags">标签</a>
		</li>
	
		<li class="menu-about">
			<a href="../../../.././about" class="menu-item-about">关于</a>
		</li>
	
		<li class="menu-gallery">
			<a href="../../../.././gallery" class="menu-item-gallery">相册</a>
		</li>
	

	
		<li class="menu-search">
			<a href="javascript:;" class="popup-trigger">搜索</a>
		</li>
	
</ul>
			</div>				
		</div>

		<!-- motto -->
		<div class="h-body">	
			
				<p class="motto"></p>
			
		</div>
		
		<!-- others: such as time... -->			
		<div class="h-footer">
			<a href="javascript:;" id="read-more"><i class="fa fa-angle-double-down" aria-hidden="true"></i>
			</a>

			
				<!-- 
	This is only a demo, please go to "https://time.is" to set your city time! 
-->
<style type="text/css">
	.header-date {
		font-size: 1.6rem;
		color: #fff;
		position: absolute;
		bottom: 5px;
		right: 1rem;
		writing-mode: tb-rl;
	}	
	
	.header-date a {
		border-bottom: none;
	}

	@media only screen and (max-width: 768 ) {
		.header-date {
			font-size: 1rem;
		}			
	}
</style>
<div class="header-date">
	<a href="https://time.is/Beijing" id="time_is_link" rel="nofollow"></a>
	<span id="Beijing_z43d"></span>
</div>
<script src="//widget.time.is/zh.js"></script>
<script>
	time_is_widget.init({
		Beijing_z43d:{
			template:"DATE", 
			date_format:"year年 monthname dnum日"
		}
	});
</script>
			
		</div>
	</div>
</header>

<div id="navigation-hide">
	<!-- Progress bar -->
	<div id="progress-bar"></div>

	<!-- Progress percent -->
	<div id="progress-percentage"><h1>0.0%</h1></div>

	<div class="toc-switch"><span class="switch-button">目录</span></div>

	<!-- Page title -->
	<p>
		
			当前文章&nbsp;:&nbsp;《mysql高级》
		
	</p>

	<!-- Nav trigger for navigation-H-->
	<a class="nav-trigger"><span></span></a>
</div>

<!-- Navigation in div(id="navigation-H") -->
<nav class="nav-container" id="cd-nav">
	<div class="nav-header">
		<h3>Navigation</h3>
		<a href="javascript:;" class="nav-close"></a>
	</div>
	<div class="nav-body">
		<ul>
	
		<li class="menu-home">
			<a href="../../../../index.html" class="menu-item-home">主页</a>
		</li>
	
		<li class="menu-archive">
			<a href="../../../.././archives" class="menu-item-archive">归档</a>
		</li>
	
		<li class="menu-categories">
			<a href="../../../.././categories" class="menu-item-categories">分类</a>
		</li>
	
		<li class="menu-tags">
			<a href="../../../.././tags" class="menu-item-tags">标签</a>
		</li>
	
		<li class="menu-about">
			<a href="../../../.././about" class="menu-item-about">关于</a>
		</li>
	
		<li class="menu-gallery">
			<a href="../../../.././gallery" class="menu-item-gallery">相册</a>
		</li>
	

	
		<li class="menu-search">
			<a href="javascript:;" class="popup-trigger">搜索</a>
		</li>
	
</ul>
	</div>
	<div class="nav-footer">
		<ul>
	
		<li>
			<a href="http://github.com/addyzk" target="_blank">
				<i class="fa fa-github"></i>
			</a>
		</li>
	
		<li>
			<a href="https://weibo.com/u/2943549915" target="_blank">
				<i class="fa fa-weibo"></i>
			</a>
		</li>
	
		<li>
			<a href="http://github.com/" target="_blank">
				<i class="fa fa-pinterest"></i>
			</a>
		</li>
	
		<li>
			<a href="http://github.com/" target="_blank">
				<i class="fa fa-instagram"></i>
			</a>
		</li>
	
		<li>
			<a href="http://github.com/" target="_blank">
				<i class="fa fa-twitter"></i>
			</a>
		</li>
	
		<li>
			<a href="../../../../atom.xml" target="_blank">
				<i class="fa fa-rss"></i>
			</a>
		</li>
			
</ul>

	</div>
</nav>
			
		<!--main-->
		<main>
			<!--
	时间：2018-11-17
	描述：
		插件名称：katelog.min.js
		插件作者：KELEN
		插件来源: https://github.com/KELEN/katelog
-->

	
		<div class="layout-toc">
			<div id="layout-toc">
				<div class="k-catelog-list" id="catelog-list" data-title="文章目录"></div>
			</div>
		</div>

		
		<script src="../../../../plugin/toc/katelog.min.js"></script>

		
	 

<div class="layout-post">
	<div id="layout-post">
	<div class="article-title">
		<i class="fa fa-paper-plane-o" aria-hidden="true"></i>
		
	<a href itemprop="url">
		mysql高级
	</a>

	</div>

	<div class="article-meta">
		<span>
			<i class="fa fa-calendar"></i>
			


	发布于

	<a href itemprop="url">
		<time datetime="2018-10-12T12:17:45.000Z" itemprop="datePublished">
	  		2018-10-12
	  </time>
	</a>
	&nbsp;





			




	更新于

	<a href itemprop="url">
		<time datetime="2018-10-12T12:17:45.000Z" itemprop="dateUpdated">
	  		2019-05-18
	  </time>
	</a> 



		</span>
		<span>
			<i class="fa fa-tags"></i>
			
	
		<a href="../../../../tags/数据库/" class=" ">
			数据库
		</a>
	
		
		</span>
		
		

	
    <span class="leancloud_visitors" id="_visitors" data-url data-title="mysql高级">
       	<i class="fa fa-eye"></i>
       	热度
        <i class="leancloud_visitors_count" id="leancloud_visitors_count">0</i>
    </span>
    



	
    <span class="leancloud_likes" id="_likes" data-url data-title="mysql高级" rel="unlike">
        <i class="fa fa-heart"></i>
        喜欢
        <i class="leancloud_likes_count" id="leancloud_likes_count">0</i>
    </span>

	</div>

	<div class="article-content" id="article-content">
		<p>#mysql数据库</p>
<h2 id="一、高级"><a href="#一、高级" class="headerlink" title="一、高级"></a>一、高级</h2><h3 id="1-1-子查询"><a href="#1-1-子查询" class="headerlink" title="1.1 子查询"></a>1.1 子查询</h3><ul>
<li>子查询嵌入到其他查询语句中查询语句，子查询只能出现在from，where中</li>
<li>子查询不要用select *,exists除外</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> title </span><br><span class="line"><span class="keyword">from</span> forum </span><br><span class="line"><span class="keyword">where</span> uid <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> php_user <span class="keyword">where</span> <span class="keyword">name</span>=<span class="string">'王琨'</span> );</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> blog_article <span class="keyword">where</span> cid <span class="keyword">in</span> (</span><br><span class="line">    <span class="keyword">select</span> cid <span class="keyword">from</span> blog_category <span class="keyword">where</span> <span class="keyword">name</span>=<span class="string">'心情'</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> (<span class="keyword">select</span> uid,username,gender <span class="keyword">from</span> blog_user <span class="keyword">where</span> gender=<span class="string">'男'</span>) <span class="keyword">as</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>
<p>###1.2 多表查询(<code>*****</code>)</p>
<figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">for</span> category <span class="keyword">in</span> blog_category:</span><br><span class="line">   <span class="keyword">for</span> <span class="selector-tag">article</span> <span class="keyword">in</span> blog_article:</span><br><span class="line">       <span class="keyword">if</span> category<span class="selector-class">.cid</span> == <span class="selector-tag">article</span><span class="selector-class">.cid</span>:</span><br><span class="line">            #放到结果集中</span><br></pre></td></tr></table></figure>
<ul>
<li>多表连接必须<strong>要有连接条件</strong>，否则结果没有意义</li>
<li>多表连接有两种写法：隐式(标准sql)和显式内连接</li>
<li><p>隐式(标准sql)连接 ：  连接条件写到where字句中</p>
<figure class="highlight routeros"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">select title,content,name,publish_time </span><br><span class="line"><span class="keyword">from</span><span class="built_in"> user </span>u,forum f  #给表起一个别名，方便书写</span><br><span class="line">where u.id = f.uid; 在where写链接条件</span><br><span class="line">			 </span><br><span class="line">select title,content,name,publish_time </span><br><span class="line"><span class="keyword">from</span><span class="built_in"> user </span>u,forum f </span><br><span class="line">where u.id = f.uid <span class="keyword">and</span> <span class="attribute">name</span>=<span class="string">'王琨'</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">select a.username,b.name ,c.title</span><br><span class="line">    -&gt; <span class="keyword">from</span> bbs_user a,bbs_category b,bbs_forum c</span><br><span class="line">    -&gt; where b.cid = c.cid <span class="keyword">and</span> c.uid = a.uid;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<p>显示内连接（inner join）</p>
 <figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; <span class="keyword">select</span> a.uid,username,<span class="built_in">title</span>,content </span><br><span class="line">    -&gt; <span class="keyword">from</span> bbs_user a <span class="keyword">inner</span> <span class="keyword">join</span> bbs_forum b <span class="keyword">on</span> a.uid =b.uid #关联条件</span><br><span class="line">    -&gt; <span class="keyword">where</span> a.uid &lt; <span class="number">5</span>;  #过滤条件</span><br><span class="line">    </span><br><span class="line">    <span class="keyword">select</span> username,name,<span class="built_in">title</span></span><br><span class="line">    -&gt; <span class="keyword">from</span> bbs_user a <span class="keyword">inner</span> <span class="keyword">join</span> bbs_forum c <span class="keyword">on</span> c.uid =a.uid </span><br><span class="line">    -&gt; <span class="keyword">inner</span> <span class="keyword">join</span> bbs_category b <span class="keyword">on</span> c.cid = b.cid;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">title</span>,remark,username <span class="keyword">from</span> blog_remark r <span class="keyword">join</span> blog_article a <span class="keyword">on</span> r.aid = a.aid </span><br><span class="line">                                                <span class="keyword">join</span> blog_user u <span class="keyword">on</span> r.uid = u.uid;</span><br><span class="line"><span class="keyword">select</span>后的字段如果在多个表中都有，引用的时候必须加上表名.字段名</span><br></pre></td></tr></table></figure>
<p>表的自身连接</p>
 <figure class="highlight asciidoc"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">select * from areainfo a,areainfo b where a.pid=b.code and  a.name=<span class="emphasis">'青河县'</span>;</span><br><span class="line"><span class="code">+--------+</span>-----------<span class="code">+--------+</span>--------<span class="code">+-----------------+</span>--------+</span><br><span class="line">| code   | name      | pid    | code   | name            | pid    |</span><br><span class="line"><span class="code">+--------+</span>-----------<span class="code">+--------+</span>--------<span class="code">+-----------------+</span>--------+</span><br><span class="line">| 654325 | 青河县    | 654300 | 654300 | 阿勒泰地区      | 650000 |</span><br><span class="line"><span class="code">+--------+</span>-----------<span class="code">+--------+</span>--------<span class="code">+-----------------+</span>--------+</span><br><span class="line">1 row in set (0.01 sec)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">//表的字段可以直接连接</span></span><br><span class="line">select * from zzl<span class="emphasis">_student where sno = monitor and class='95031';</span></span><br></pre></td></tr></table></figure>
<h3 id="1-3-外连接"><a href="#1-3-外连接" class="headerlink" title="1.3 外连接"></a>1.3 外连接</h3><blockquote>
<p>两张表关联查询时，根据以那种表为主可以分为左外连接和右外连接</p>
</blockquote>
<p>左外连接  </p>
<p>​      以左表为主，如果右边的表里没有匹配的记录，则添加一个万能记录（各个字段都为null)与之连接</p>
<pre><code>select username,r.* from blog_user u left join  blog_remark r on u.uid = r.uid
+-----------+------+-------------+------+------+------------+-----------+
| username  | rid  | remark      | aid  | uid  | remarktime | isdisplay |
+-----------+------+-------------+------+------+------------+-----------+
| 萧峰      |    1 | adsafd      |    1 |    1 | NULL       |         0 |
| 慕容复    |    2 | kdkdkdkd    |    2 |    3 | NULL       |         0 |
| 丁春秋    |    3 | ooooooooooo |    3 |    4 | NULL       |         0 |
| 丁春秋    |    4 | ppppp       |    2 |    4 | NULL       |         0 |
| 阿朱      | NULL | NULL        | NULL | NULL | NULL       |      NULL |
| 阿碧      | NULL | NULL        | NULL | NULL | NULL       |      NULL |
| 谢晓峰    | NULL | NULL        | NULL | NULL | NULL       |      NULL |
+-----------+------+-------------+------+------+------------+-----------+
</code></pre><p>右外连接（right join）</p>
<p>以右表为主，如果左边的表里没有匹配记录，则增加一个万能记录与之连接</p>
<h3 id="1-4-集合操作"><a href="#1-4-集合操作" class="headerlink" title="1.4 集合操作"></a>1.4 集合操作</h3><p>可以使用union将两个查询结果合并，mysql只支持并，不支持差和交</p>
<p>两个结果集中字段数一样，对应字段类型兼容</p>
<p>自动去除重复记录,不去除重复记录可以用 union all</p>
<p>order by 放到最后</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> = <span class="string">'95031'</span></span><br><span class="line"><span class="keyword">union</span> <span class="keyword">all</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> ssex=<span class="string">'女'</span>;</span><br></pre></td></tr></table></figure>
<h3 id="1-5-内部函数"><a href="#1-5-内部函数" class="headerlink" title="1.5 内部函数"></a>1.5 内部函数</h3><p>字符串函数</p>
<table>
<thead>
<tr>
<th>函数</th>
<th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>char_length(<em>str</em>)</td>
<td>获取字符串的字符个数</td>
</tr>
<tr>
<td>length(str)</td>
<td>获取字符串的字节数</td>
</tr>
<tr>
<td>concat(s1, s2, … , sn)</td>
<td>连接s1, s2, …, sn 为一个字符串</td>
</tr>
<tr>
<td>lower(str)</td>
<td>将字符串str中所有的字符转换为小写</td>
</tr>
<tr>
<td>upper(str)</td>
<td>将字符串str中所有的字符转换为大写</td>
</tr>
<tr>
<td>left(str, x)</td>
<td>返回字符串str最左边的x个字符</td>
</tr>
<tr>
<td>right(str, y)</td>
<td>返回字符串str最右边的y个字符</td>
</tr>
<tr>
<td>lpad(str, n, pad)</td>
<td>用字符串pad对str最左边进行填充， 直到长度为n个字符长度</td>
</tr>
<tr>
<td>rpad(str, n, pad)</td>
<td>用字符串pad对str最右边进行填充， 直到长度为n个字符长度</td>
</tr>
<tr>
<td>ltrim(str)</td>
<td>去掉str中最左边的空格</td>
</tr>
<tr>
<td>rtrim(str)</td>
<td>去掉str中最右边的空格</td>
</tr>
<tr>
<td>trim(str)</td>
<td>去掉字符串str两边的空格</td>
</tr>
<tr>
<td>repeat(str, x)</td>
<td>返回str中重复出现x次的结果</td>
</tr>
<tr>
<td>replace(str, a, b)</td>
<td>将字符串str中的a更换为b</td>
</tr>
<tr>
<td>insert(str, x, y, instr)</td>
<td>将字符串str从第x位置开始， y个字符长度的子字符串替换为字符串instr</td>
</tr>
<tr>
<td>strcmp（s1, s2）</td>
<td>比较字符串s1, s2</td>
</tr>
<tr>
<td>substring(str, x, y)</td>
<td>返回字符串str x位置开始y个字符长度的字符串</td>
</tr>
</tbody>
</table>
<p>日期函数</p>
<table>
<thead>
<tr>
<th>函数名</th>
<th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>curdate()</td>
<td>得到当前日期</td>
</tr>
<tr>
<td>curtime()</td>
<td>得到当前时间</td>
</tr>
<tr>
<td>now()</td>
<td>得到当前日期和时间</td>
</tr>
<tr>
<td>year(date)</td>
<td>得到date的年份</td>
</tr>
<tr>
<td>month(date)</td>
<td>得到date的月份</td>
</tr>
<tr>
<td>day(date)</td>
<td>得到date的天</td>
</tr>
<tr>
<td>hour(time)</td>
<td>得到time的小时</td>
</tr>
<tr>
<td>minute(time)</td>
<td>得到time 的分钟</td>
</tr>
<tr>
<td>second(time)</td>
<td>得到time的秒</td>
</tr>
<tr>
<td>week(date)</td>
<td>得到date是一年中的第几周</td>
</tr>
<tr>
<td>date_format(date,fmt)</td>
<td>按格式化串fmt返回date的日期字符串</td>
</tr>
</tbody>
</table>
<p> select DATE_FORMAT(now(),’%Y- %m-%d %H:%i:%s’);  </p>
<p>数学函数</p>
<table>
<thead>
<tr>
<th>函数名</th>
<th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>abs(x)</td>
<td>求x的绝对值</td>
</tr>
<tr>
<td>ceil(x)</td>
<td>向上取整</td>
</tr>
<tr>
<td>floor(x)</td>
<td>向下取整</td>
</tr>
<tr>
<td>round(x,d)</td>
<td>四舍五入，d为保留小数的位数</td>
</tr>
<tr>
<td>pow(x,y)</td>
<td>x的y次幂</td>
</tr>
<tr>
<td>rand()</td>
<td>0~1之间的随机小数</td>
</tr>
<tr>
<td>mod(x,y)</td>
<td>等同于x % y,求x对y的模</td>
</tr>
</tbody>
</table>
<p>其它函数</p>
<table>
<thead>
<tr>
<th>函数名</th>
<th>功能</th>
</tr>
</thead>
<tbody>
<tr>
<td>convert(expr as type)/cast(expr as type)</td>
<td>将表达式expr转换为type类型，type可以是：char(n)、date、datetime、integer、decimal</td>
</tr>
<tr>
<td>md5(str)</td>
<td>计算str的哈希值，返回一个 32位十六进制数字的二进制字符串</td>
</tr>
<tr>
<td>sha1(str)/sha(str)</td>
<td>计算str的哈希值，返回一个 40位十六进制数字的二进制字符串</td>
</tr>
</tbody>
</table>
<h2 id="二、数据控制"><a href="#二、数据控制" class="headerlink" title="二、数据控制"></a>二、数据控制</h2><p>###2.1 事务</p>
<p>ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种<a href="https://baike.baidu.com/item/%E7%89%B9%E6%80%A7/3128227" target="_blank" rel="noopener">特性</a>，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。</p>
<p>事务把一组操作看做一个整体，要不都操作成功，要不都操作失败 。(ACID)</p>
<p>表的数据库引擎必须是innodb，innodb支持事物，myisam不支持事务</p>
<p>修改表引擎：alter table  表名 engine = innodb</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">查询是否为自动提交</span><br><span class="line"><span class="keyword">select</span> @@autocommit  (<span class="number">1</span>为自动提交   <span class="number">0</span>为手动提交)</span><br><span class="line"></span><br><span class="line">关闭自动提交</span><br><span class="line"><span class="keyword">set</span> autocommit = <span class="number">0</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">start</span> <span class="keyword">transaction</span> /<span class="keyword">begin</span></span><br><span class="line"></span><br><span class="line">一组操作</span><br><span class="line"><span class="keyword">commit</span>/<span class="keyword">rollback</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">commit</span> 提交 会把数据写到硬盘</span><br><span class="line"><span class="keyword">rollback</span> 回滚 撤销操作</span><br></pre></td></tr></table></figure>
<p>###2.2 授权管理(了解)</p>
<p>创建用户</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="string">'用户名'</span>@<span class="string">'服务器地址'</span> <span class="keyword">identified</span> <span class="keyword">by</span> <span class="string">'密码'</span></span><br></pre></td></tr></table></figure>
<p>删除用户</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span>  <span class="string">'用户名'</span>@<span class="string">'服务器地址'</span></span><br></pre></td></tr></table></figure>
<p>修改密码</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">修改当前登录用户</span><br><span class="line"><span class="keyword">set</span> <span class="keyword">password</span> = <span class="keyword">password</span>(<span class="string">'123456'</span>);</span><br><span class="line">			</span><br><span class="line">一般管理员可以修改任意用户密码</span><br><span class="line"><span class="keyword">set</span> <span class="keyword">password</span> <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span> = <span class="keyword">password</span>(<span class="string">'2333'</span>);</span><br></pre></td></tr></table></figure>
<p>刷新</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">flush</span> <span class="keyword">privileges</span></span><br></pre></td></tr></table></figure>
<p>授权</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">grant</span> 权限  <span class="keyword">on</span> 数据库.表  <span class="keyword">to</span> <span class="string">'用户名'</span>@<span class="string">'服务器地址'</span></span><br><span class="line"> <span class="keyword">grant</span> <span class="keyword">all</span> <span class="keyword">on</span> *.* <span class="keyword">to</span> <span class="string">'dd'</span>@<span class="string">'localhost'</span></span><br><span class="line"> *.* 所有数据库的所有表</span><br><span class="line"> <span class="keyword">all</span> 代表所有权限  </span><br><span class="line"> 权限包括：<span class="keyword">select</span>、<span class="keyword">update</span>、<span class="keyword">delete</span>、<span class="keyword">alter</span>、<span class="keyword">insert</span></span><br></pre></td></tr></table></figure>
<p>回收</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">revoke</span> <span class="keyword">select</span> <span class="keyword">on</span> test.stars <span class="keyword">from</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span><br></pre></td></tr></table></figure>
<h2 id="三、索引"><a href="#三、索引" class="headerlink" title="三、索引"></a>三、索引</h2><p>索引就像图书的目录，可以加快查询速度</p>
<h3 id="3-1-索引的优点"><a href="#3-1-索引的优点" class="headerlink" title="3.1 索引的优点"></a>3.1 索引的优点</h3><ul>
<li>可以大大加快数据的检索速度</li>
<li>唯一索引可以保证数据的唯一性</li>
<li>可以降低分组、排序的时间</li>
<li>可以使用查询优化器提高系统性能</li>
</ul>
<h3 id="3-2-索引的缺点"><a href="#3-2-索引的缺点" class="headerlink" title="3.2 索引的缺点"></a>3.2 索引的缺点</h3><ul>
<li>建立索引会建立对应索引文件，占用大量空间</li>
<li>建立索引会降低增、删、改的效率</li>
</ul>
<h3 id="3-3-不建立索引"><a href="#3-3-不建立索引" class="headerlink" title="3.3 不建立索引"></a>3.3 不建立索引</h3><ul>
<li>频繁更新的字段不要建立索引</li>
<li>没出现在where、having，不要建立索引</li>
<li>数据量少的表没有必要建立索引</li>
<li>唯一性比较差的字段不要建立索引</li>
</ul>
<h3 id="3-4-索引分类"><a href="#3-4-索引分类" class="headerlink" title="3.4 索引分类"></a>3.4 索引分类</h3><h4 id="普通索引"><a href="#普通索引" class="headerlink" title="普通索引"></a>普通索引</h4><pre><code>create index 索引名 on 表名(字段 asc/desc) 默认asc升序
</code></pre><h4 id="唯一索引"><a href="#唯一索引" class="headerlink" title="唯一索引"></a>唯一索引</h4><p>  在唯一索引所在列不能有重复值，增加和修改会受影响。</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span>  <span class="keyword">unique</span> <span class="keyword">index</span> 索引名 <span class="keyword">on</span> 表名(字段 <span class="keyword">asc</span>/<span class="keyword">desc</span>) 默认<span class="keyword">asc</span>升序</span><br></pre></td></tr></table></figure>
<h4 id="主键索引"><a href="#主键索引" class="headerlink" title="主键索引"></a>主键索引</h4><p>  创建表，主键索引会自动添加，要求在主键上不能有重复值，不能有空值</p>
<h4 id="复合索引（联合索引）-索引了多个列"><a href="#复合索引（联合索引）-索引了多个列" class="headerlink" title="复合索引（联合索引） 索引了多个列"></a>复合索引（联合索引） 索引了多个列</h4><p>使用联合索引，必须包含左前缀。  （a,b,c)</p>
<ul>
<li>a</li>
<li>a,b</li>
<li>a,b,c</li>
</ul>
<h4 id="全文索引（了解）"><a href="#全文索引（了解）" class="headerlink" title="全文索引（了解）"></a>全文索引（了解）</h4><p>   一般会用全文索引服务器(sphinx)，不会直接创建全文索引</p>
  <figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span>  FULLTEXT <span class="keyword">index</span> 索引名 <span class="keyword">on</span> 表名(字段 <span class="keyword">asc</span>/<span class="keyword">desc</span>)</span><br></pre></td></tr></table></figure>
<h4 id="删除索引"><a href="#删除索引" class="headerlink" title="删除索引"></a>删除索引</h4>  <figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">index</span> 索引名 <span class="keyword">on</span> 表</span><br></pre></td></tr></table></figure>
<h4 id="查看索引"><a href="#查看索引" class="headerlink" title="查看索引"></a>查看索引</h4>   <figure class="highlight jboss-cli"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">show index from 表 \G</span><br><span class="line"></span><br><span class="line"><span class="comment">#查看sql性能</span></span><br><span class="line">explain select sno,sname from student where class='1812'\G;</span><br><span class="line">mysql&gt; explain select sno,sname from student where sclass='1812' ;</span><br><span class="line">+<span class="params">----</span>+<span class="params">-------------</span>+<span class="params">---------</span>+<span class="params">------------</span>+<span class="params">------</span>+<span class="params">---------------</span>+<span class="params">------</span>+<span class="params">---------</span>+<span class="params">------</span>+<span class="params">------</span>+<span class="params">----------</span>+<span class="params">-------------</span>+</span><br><span class="line">| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |</span><br><span class="line">+<span class="params">----</span>+<span class="params">-------------</span>+<span class="params">---------</span>+<span class="params">------------</span>+<span class="params">------</span>+<span class="params">---------------</span>+<span class="params">------</span>+<span class="params">---------</span>+<span class="params">------</span>+<span class="params">------</span>+<span class="params">----------</span>+<span class="params">-------------</span>+</span><br><span class="line">|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |</span><br><span class="line">+<span class="params">----</span>+<span class="params">-------------</span>+<span class="params">---------</span>+<span class="params">------------</span>+<span class="params">------</span>+<span class="params">---------------</span>+<span class="params">------</span>+<span class="params">---------</span>+<span class="params">------</span>+<span class="params">------</span>+<span class="params">----------</span>+<span class="params">-------------</span>+</span><br><span class="line">1 row in <span class="keyword">set</span>, 1 warning <span class="params">(0.00 sec)</span></span><br><span class="line">type:  ALL  全表扫描</span><br><span class="line">       index  使用索引</span><br><span class="line">       range  在指定范围内使用索引</span><br><span class="line">       const、system 常量查询</span><br></pre></td></tr></table></figure>
<h4 id="其它创建索引的方式"><a href="#其它创建索引的方式" class="headerlink" title="其它创建索引的方式"></a>其它创建索引的方式</h4> <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表 <span class="keyword">add</span> <span class="keyword">index</span>(字段<span class="number">1</span>,字段<span class="number">2</span>,...)</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表 <span class="keyword">add</span> primary <span class="keyword">key</span>(字段<span class="number">1</span>,字段<span class="number">2</span>,...)</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表 <span class="keyword">add</span> <span class="keyword">unique</span>(字段<span class="number">1</span>,字段<span class="number">2</span>,...)</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表 <span class="keyword">add</span> fulltext(字段<span class="number">1</span>,字段<span class="number">2</span>,...)</span><br></pre></td></tr></table></figure>
<h3 id="3-5-不使用索引的情况"><a href="#3-5-不使用索引的情况" class="headerlink" title="3.5 不使用索引的情况"></a>3.5 不使用索引的情况</h3><ul>
<li>查询时的联合索引没有左前缀，不使用索引</li>
<li>or条件里，如果一方字段没有索引，则不使用索引</li>
<li>类型不对应的不会使用索引</li>
<li>like  ‘%tom’ ,如果左边是通配符，不会使用索引</li>
<li>使用!=、&lt;&gt;、not in操作，不使用索引</li>
</ul>
<h2 id="四、外键"><a href="#四、外键" class="headerlink" title="四、外键"></a>四、外键</h2><p>如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表</p>
<ul>
<li>数据表引擎必须是innodb</li>
<li>主表和从表相关的外键字段类型必须兼容</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">创建外键</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> 从表名</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> 外键名称 <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (从表的外键列) <span class="keyword">REFERENCES</span> 主表名 (主键列) </span><br><span class="line">[<span class="keyword">ON</span> <span class="keyword">DELETE</span> reference_option]</span><br><span class="line">[<span class="keyword">ON</span> <span class="keyword">UPDATE</span> reference_option]</span><br><span class="line"></span><br><span class="line">reference_option:</span><br><span class="line">RESTRICT | <span class="keyword">CASCADE</span> | <span class="keyword">SET</span> <span class="literal">NULL</span> | <span class="keyword">NO</span> <span class="keyword">ACTION</span></span><br><span class="line">  <span class="number">1.</span> <span class="keyword">CASCADE</span>: 从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。<span class="keyword">ON</span> <span class="keyword">DELETE</span> CANSCADE和<span class="keyword">ON</span> <span class="keyword">UPDATE</span> CANSCADE都被<span class="keyword">InnoDB</span>所支持。</span><br><span class="line">  </span><br><span class="line">  <span class="number">2.</span> <span class="keyword">SET</span> <span class="literal">NULL</span>: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为<span class="keyword">NOT</span> <span class="literal">NULL</span>时才有效。<span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">SET</span> <span class="literal">NULL</span>和<span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">SET</span> <span class="keyword">SET</span> <span class="literal">NULL</span>都被<span class="keyword">InnoDB</span>所支持。</span><br><span class="line"></span><br><span class="line">  <span class="number">3.</span> <span class="keyword">NO</span> <span class="keyword">ACTION</span>: <span class="keyword">InnoDB</span>拒绝删除或者更新父表。</span><br><span class="line"></span><br><span class="line">  <span class="number">4.</span> RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者<span class="keyword">NO</span> <span class="keyword">ACTION</span>）和忽略<span class="keyword">ON</span> <span class="keyword">DELETE</span>或者<span class="keyword">ON</span> <span class="keyword">UPDATE</span>选项的效果是一样的。</span><br><span class="line"></span><br><span class="line">删除外键</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> 从表 <span class="keyword">DROP</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> 外键名</span><br></pre></td></tr></table></figure>
<h2 id="五、视图"><a href="#五、视图" class="headerlink" title="五、视图"></a>五、视图</h2><p>有时候经常会遇到复杂的查询，写起来比较麻烦，这时候我们可以使用视图简化查询。视图就是固化的sql语句，可以不把视图当做基本表使用</p>
<p>不要在视图上进行增、删、改</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">创建视图</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> 视图名(字段列表) <span class="keyword">as</span> </span><br><span class="line"><span class="keyword">select</span>子句</span><br><span class="line"></span><br><span class="line">删除视图</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">view</span> 视图名</span><br></pre></td></tr></table></figure>
<h2 id="六、数据库备份与恢复"><a href="#六、数据库备份与恢复" class="headerlink" title="六、数据库备份与恢复"></a>六、数据库备份与恢复</h2><p>备份</p>
<figure class="highlight 1c"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">不用登录mysql，直接执行mysqldump命令,将指定数据库备份到家目录下的指定文件</span><br><span class="line">mysqldump –uroot –p 数据库名 &gt; <span class="symbol">~/备份文件名.sql</span>;</span><br></pre></td></tr></table></figure>
<p>恢复</p>
<figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">首先要创建一个mysql数据库，然后退出mysql，执行以下命令</span><br><span class="line">mysql -uroot –<span class="selector-tag">p</span> 数据库名 &lt; ~/备份文件.sql</span><br></pre></td></tr></table></figure>
<h2 id="七、pymysql操作mysql数据库"><a href="#七、pymysql操作mysql数据库" class="headerlink" title="七、pymysql操作mysql数据库"></a>七、pymysql操作mysql数据库</h2><p>安装pymysql</p>
<figure class="highlight cmake"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pip <span class="keyword">install</span> pymysql</span><br></pre></td></tr></table></figure>
<h3 id="7-1-pymysql操作数据库的五行拳"><a href="#7-1-pymysql操作数据库的五行拳" class="headerlink" title="7.1 pymysql操作数据库的五行拳"></a>7.1 pymysql操作数据库的五行拳</h3><ol>
<li><p>连接数据库</p>
<p>使用Connect方法连接数据库</p>
<figure class="highlight routeros"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">pymysql.Connections.Connection(<span class="attribute">host</span>=None, <span class="attribute">user</span>=None, <span class="attribute">password</span>=<span class="string">''</span>, <span class="attribute">database</span>=None, <span class="attribute">port</span>=0,  <span class="attribute">charset</span>=<span class="string">''</span>)</span><br><span class="line">参数说明：</span><br><span class="line">    host – 数据库服务器所在的主机。</span><br><span class="line">   <span class="built_in"> user </span>– 登录用户名。</span><br><span class="line">    password – 登录用户密码。</span><br><span class="line">    database – 连接的数据库。</span><br><span class="line">   <span class="built_in"> port </span>– 数据库开放的端口。（默认: 3306）</span><br><span class="line">    charset – 连接字符集。</span><br><span class="line">返回值：</span><br><span class="line">   返回连接对象</span><br><span class="line">   </span><br><span class="line">例子：</span><br><span class="line">link = pymysql.Connect(<span class="attribute">host</span>=<span class="string">'localhost'</span>, <span class="attribute">port</span>=3306, <span class="attribute">user</span>=<span class="string">'root'</span>, <span class="attribute">password</span>=<span class="string">'123456'</span>, <span class="attribute">db</span>=<span class="string">'zzl'</span>, <span class="attribute">charset</span>=<span class="string">'utf8'</span>)</span><br></pre></td></tr></table></figure>
<ul>
<li>连接对象方法</li>
</ul>
<p>| 方法                  | 说明              |<br>| ——————- | ————— |<br>| begin()             | 开启事务            |<br>| commit()            | 提交事务            |<br>| cursor(cursor=None) | 创建一个游标用来执行sql语句 |<br>| rollback()          | 回滚事务            |<br>| close()             | 关闭连接            |<br>| select_db(db)       | 选择数据库           |</p>
</li>
<li><p>创建游标</p>
<figure class="highlight arduino"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">cursor</span> = link.<span class="built_in">cursor</span>()</span><br><span class="line"><span class="built_in">print</span>(<span class="built_in">cursor</span>.rowcount) #打印受影响行数</span><br></pre></td></tr></table></figure>
<p>| 方法                        | 说明                                       |<br>| ————————- | —————————————- |<br>| close()                   | 关闭游标                                     |<br>| execute(query, args=None) | 执行单条语句，传入需要执行的语句，是string类型；同时可以给查询传入参数，参数可以是tuple、list或dict。执行完成后，会返回执行语句的影响行数。 |<br>| fetchone()                | 取一条数据                                    |<br>| fetchmany(n)              | 取多条数据                                    |<br>| fetchall()                | 取所有数据                                    |</p>
</li>
<li><p>执行sql语句</p>
<figure class="highlight ini"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 执行sql语句</span></span><br><span class="line"><span class="attr">sql</span> = <span class="string">'select * from user1'</span></span><br><span class="line"><span class="comment"># 执行完sql语句，返回受影响的行数</span></span><br><span class="line"><span class="attr">num</span> = cursor.execute(sql)</span><br></pre></td></tr></table></figure>
</li>
<li><p>获取结果集</p>
<figure class="highlight arduino"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">result1 = <span class="built_in">cursor</span>.fetchone()</span><br><span class="line"><span class="built_in">print</span>(result1)</span><br></pre></td></tr></table></figure>
</li>
<li><p>关闭连接</p>
<figure class="highlight arduino"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">cursor</span>.<span class="built_in">close</span>()</span><br><span class="line">link.<span class="built_in">close</span>()</span><br></pre></td></tr></table></figure>
</li>
</ol>
<p>注意：</p>
<p>写完代码后，需要将py文件添加可执行权限 </p>
<figure class="highlight stylus"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">sudo chmod +x conndb.py</span><br><span class="line">./conndb.py</span><br></pre></td></tr></table></figure>
<h3 id="7-2-pymysql中事务处理"><a href="#7-2-pymysql中事务处理" class="headerlink" title="7.2 pymysql中事务处理"></a>7.2 pymysql中事务处理</h3><p>pymysql默认是没有开启自动提交事务，所以我们如果进行增、删、改，就必须手动提交或回滚事务。</p>
<figure class="highlight vim"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">sql = <span class="string">'delete from user where id=%s'</span> % user_id</span><br><span class="line"></span><br><span class="line"># 如果要执行增删改语句的时候，下面的就是固定格式</span><br><span class="line"><span class="keyword">try</span>:</span><br><span class="line">	<span class="built_in">cursor</span>.<span class="keyword">execute</span>(sql)</span><br><span class="line">	# 如果全部执行成功，提交事务</span><br><span class="line">	link.commit()</span><br><span class="line">	<span class="keyword">print</span>(<span class="built_in">cursor</span>.lastrowid) #获取最后插入记录的自增id号</span><br><span class="line">except Exception <span class="keyword">as</span> <span class="keyword">e</span>:</span><br><span class="line">	<span class="keyword">print</span>(<span class="keyword">e</span>)</span><br><span class="line">	link.rollback()</span><br><span class="line"><span class="keyword">finally</span>:</span><br><span class="line">	<span class="built_in">cursor</span>.<span class="keyword">close</span>()</span><br><span class="line">	link.<span class="keyword">close</span>()</span><br></pre></td></tr></table></figure>
	
	</div>
	
	<div id="current-post-cover" data-scr="https://addyzk.github.io/Random-img/15.jpg"></div>

	<!-- relate post, comment...-->
	<div class="investment-container">
		<div class="investment-header">
			<div class="investment-title-1">
				<div class="on">相关文章</div>
				<div>评论</div>
				<div>分享</div>
			</div>
			<div class="investment-title-2">	            
				
	<span>
		<a href="javascript: window.scrollTo(0, 0);">返回顶部</a>
		
			<a href="../../../../2019/04/18/Python进阶/" title="python进阶" rel="prev">
				&laquo;上一篇
			</a>
		
		
			<a href="../../../09/17/sql优化/" title="mysql优化" rel="next">
				下一篇&raquo;
			</a>
			
	</span>


      		
			</div>	
		</div>
		
		<div class="investment-content">
			<div class="investment-content-list">
				

<div class="relate-post">
	
		<ul>
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../09/17/sql优化/" title="mysql优化">
								mysql优化			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								九月 17日, 2018				
							</p>
							<p class="relate-post-content">
								mysql优化一、数据库设计原则数据库设计的三范式学生表(学号，姓名，生日，课程名，课程成绩，等级，学院，院长)



学号
姓名
生日
课程号
课程名
成绩
等级
学院
院长




10001
张三
1998.1.1
tp3021...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../09/17/sql优化/" title="mysql优化">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/16.jpg" alt="mysql优化">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../05/12/redis/" title="redis">
								redis			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								五月 12日, 2018				
							</p>
							<p class="relate-post-content">
								redis教程：概述redis是一种支持分布式的nosql数据库,他的数据是保存在内存中，同时redis可以定时把内存数据同步到磁盘，即可以将数据持久化，并且他比memcached支持更多的数据结构(string,list列表[队列和...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../05/12/redis/" title="redis">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/10.jpg" alt="redis">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../04/14/mysql/" title="mysql">
								mysql			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								四月 14日, 2018				
							</p>
							<p class="relate-post-content">
								数据库一、数据库基本概念数据的仓库:  
数据库服务器–&gt;数据库—&gt;表–&gt;记录-&gt;属性
1sudo apt-get install -y mysql-server mysql-client
二、SQL基本可分为...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../04/14/mysql/" title="mysql">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/13.jpg" alt="mysql">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../../2019/05/28/linux/" title="linux">
								linux			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								五月 28日, 2019				
							</p>
							<p class="relate-post-content">
								一、课程简介
linux服务器配置及常用命令

开发软件配置及服务环境的搭建

软件的安装和配置

mysql数据库使用、monDB使用、redius的使用

git的使用

html/css


课程学习方式
表达训练     

...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../../2019/05/28/linux/" title="linux">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/88.jpg" alt="linux">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../../2019/05/16/hello-world/" title="Hello my friend!">
								Hello my friend!			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								五月 16日, 2019				
							</p>
							<p class="relate-post-content">
								Welcome to my blog ! This is your very first post. Check documentation for more info. If you get any problems when us...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../../2019/05/16/hello-world/" title="Hello my friend!">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/2.jpg" alt="Hello my friend!">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../../2019/04/18/Python进阶/" title="python进阶">
								python进阶			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								四月 18日, 2019				
							</p>
							<p class="relate-post-content">
								Python 进阶1. PEP8 编码规范, 及开发中的一些惯例和建议练习: 规范化这段代码
12345678910from django.conf import settingsfrom user.models import *im...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../../2019/04/18/Python进阶/" title="python进阶">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/28.jpg" alt="python进阶">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../08/29/网络编程/" title="网络编程">
								网络编程			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								八月 29日, 2018				
							</p>
							<p class="relate-post-content">
								计算机之间的通信，三次握手，四次挥手！TCP/UDP协议！
17 网络编程一、网络编程1.网络编程基础
计算机网络：把分布在不同区域的计算机通过专门的设备使用通信线路连接起来，从而会形成一个庞大的网络系统，不同的计算机之间就可以进行信...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../08/29/网络编程/" title="网络编程">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/1.jpg" alt="网络编程">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../04/27/项目部署/" title="项目部署">
								项目部署			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								四月 27日, 2018				
							</p>
							<p class="relate-post-content">
								一、项目部署基本流程1234567891011- 虚拟环境- 数据库- 项目上传- 项目依赖- 数据迁移- Nginx- uWSGI- Nginx和uWSGI对接
二、虚拟环境12345678910111213141516171819...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../04/27/项目部署/" title="项目部署">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/99.jpg" alt="项目部署">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../04/23/git/" title="Git!">
								Git!			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								四月 23日, 2018				
							</p>
							<p class="relate-post-content">
								Git 的基本认识，常用命令，以及使用流程，冲突管理分支管理等。
Git1.git简介1234- workspace 工作区- index 暂存区- repository 本地版本库- remote 远程仓库
2.常用git命令1所有...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../04/23/git/" title="Git!">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/47.jpg" alt="Git!">
							</a>
						</div>
					</li>											
			
			
					<li>
						<div class="relate-post-text">
							<a class="relate-post-title" href="../../../02/18/Django-rest-framework/" title="DRF">
								DRF			
							</a>
							<p class="relate-post-date">
								<i class="fa fa-calendar"></i>
								二月 18日, 2018				
							</p>
							<p class="relate-post-content">
								一、RESTREST是”Representational State Transfer”缩写，即是”表现层状态转化”。而”表现层”其实指的是”资源(Resource)”的表现层。12表现层: 资源(例如图片、歌曲、用户....)状态转...
							</p>
						</div>

						<div class="relate-post-cover">
							<a href="../../../02/18/Django-rest-framework/" title="DRF">				
								
								<img class="lazy" src="/img/placeholder.jpg" data-src="https://addyzk.github.io/Random-img/31.jpg" alt="DRF">
							</a>
						</div>
					</li>											
			
			
		</ul>
	
</div>	
			</div>
			<div class="investment-content-list">
				<div class="layout-comment">

	

		

			<!-- gitalk comment -->
			<!-- show gitalk comment -->
<div id="gitalk-container"></div>

<!-- gitalk`s css & js -->
<link rel="stylesheet" href="https://unpkg.com/gitalk/dist/gitalk.css">
<link rel="stylesheet" href="/SorrisiYi/css/comment.css">
<script src="https://cdn.bootcss.com/blueimp-md5/2.10.0/js/md5.min.js"></script>
<script src="https://unpkg.com/gitalk/dist/gitalk.min.js"></script>

<script type="text/javascript">
	//Thanks O-R
	//https://github.com/gitalk/gitalk/issues/102#issuecomment-382970552
	//去除尾部匹配正则数组的字符串  
	//Remove redundant characters
	String.prototype.trimEnd = function(regStr) {
		var result = this;
		if(regStr == undefined || regStr == null || regStr == "") {
			return result;
		}
		var array = regStr.split(',');

		if(array.length > 0) {

			var c = array.shift();
			var str = this;
			var i = str.length;
			var rg = new RegExp(c);
			var matchArr = str.match(rg);

			if(matchArr != undefined && matchArr != null && matchArr.length > 0) {
				var matchStr = matchArr[0].replace(/\\/g, "\\\\").replace(/\*/g, "\\*")
					.replace(/\+/g, "\\+").replace(/\|/g, "\\|")
					.replace(/\{/g, "\\{").replace(/\}/g, "\\}")
					.replace(/\(/g, "\\(").replace(/\)/g, "\\)")
					.replace(/\^/g, "\\^").replace(/\$/g, "\\$")
					.replace(/\[/g, "\\[").replace(/\]/g, "\\]")
					.replace(/\?/g, "\\?").replace(/\,/g, "\\,")
					.replace(/\./g, "\\.").replace(/\&/g, "\\&");
				matchStr = matchStr + '$';
				result = str.replace(new RegExp(matchStr), "");
			}

			if(array.length > 0) {
				return result.trimEnd(array.join())
			} else {
				return result;
			}
		}
	};

	//Create gitalk
	var gitalk = new Gitalk({
		clientID: 'fa840d66074e8a6722c9',
		clientSecret: '039c6b559c7710f00e235c26795a2e2ecb613161',
		//id: window.location.pathname,
		//id: decodeURI(window.location.pathname),
		//id: (window.location.pathname).split("/").pop().substring(0, 49),
		id: decodeURI( md5( location.href.trimEnd('#.*$,\\?.*$,index.html$') ) ),
		repo: 'annie-gitalk',
		owner: 'SorrisoYi',
		admin: 'SorrisoYi',
		distractionFreeMode: 'true',
	})
	gitalk.render('gitalk-container');
</script>

		
		
	

</div>
			</div>
			<div class="investment-content-list">
				<div class="layout-share">
	
	

		

			<!-- addThis share -->
			<!-- www.addthis.com/dashboard -->

	<div class="addthis_inline_share_toolbox">
	</div>

	<script type="text/javascript" src="//s7.addthis.com/js/300/addthis_widget.js#pubid=ra-xxx" async="async"></script>


		
		
	
</div>


			</div>
		</div>	
	</div>
	</div>
</div>



	<!-- leancloud -->
	<!--
	时间：2018-11-27
	描述：
		文章访问量：visitors
		文章喜欢量：likes	
		文章排行榜：topNPost
		其他得说明：
			01-Cookie相关的函数 
				https://blog.csdn.net/somehow1002/article/details/78511541（Author：somehow1002）
			02-visitors相关的函数 
				https://blog.csdn.net/u013553529/article/details/63357382（Author：爱博客大伯）
				https://notes.doublemine.me/2015-10-21-为NexT主题添加文章阅读量统计功能.html（Author：夏末）
			03-topNPost相关的函数
				https://hoxis.github.io/hexo-next-read-rank.html（Author：hoxis）
			04-likes相关的函数，
				参考了01 & 02进行简单的设计与实现
-->



	<script>
		var appid = 'bdanoXQN5zgeVyspG5e9RF7o-gzGzoHsz',
            appkey = 'RVVmwDan5FhjuwkKxenjOGSE';	  
        AnnieLeancloud(appid, appkey);         
	</script>
    

	


<!-- show math formula -->



	 
	<script src="../../../../plugin/clipboard/clipboard.js"></script>
	<script>
		// Copy code !
	    function codePreprocessing() {
	        $("#article-content .highlight").each(function() {

	            $(this).wrap('<div id="post-code"></div>');

	        })

	        $("#article-content #post-code").each(function() {

	            $(this).prepend('<nav class="copy-nav"><span><i class="code-language"></i></span></nav>');

	        })

	        $("#article-content .copy-nav").each(function() {
	            var temp = $(this).next().attr('class'),
	                language = ((temp.length > 9) && (temp != null)) ? temp.substr(10) : "none"; //why 9? Need to check language?

	            $(this).find('.code-language').text(language);

	            $(this).append('<span class="copy-btn"><i class="fa fa-copy" aria-hidden="true"></i></span>');

	        });
	    }

		function codeCopy() {
		    $('#article-content #post-code').each(function(i) {
		        var codeCopyId = 'codeCopy-' + i;

		        var codeNode = $(this).find('.code'),
		            copyButton = $(this).find('.copy-btn');

		        codeNode.attr('id', codeCopyId);
		        copyButton.attr('data-clipboard-target-id', codeCopyId);
		    })

		    
			var clipboard = new ClipboardJS('.copy-btn', {
					target: function(trigger) {
						return document.getElementById(trigger.getAttribute('data-clipboard-target-id'));
					}
		      	});

			//pure js
			function showTooltip(elem, msg) {		   
				elem.setAttribute('aria-label', msg);
				elem.setAttribute('class', 'copy-btn copy-status');
				setTimeout(function() {
					elem.setAttribute('class', 'copy-btn');
				}, 2000);
			}

			clipboard.on('success', function(e) {
			    e.clearSelection();
			    console.info('Action:', e.action);		   
			    console.info('Trigger:', e.trigger);
			    showTooltip(e.trigger, 'Copied!');
			    
			});
			clipboard.on('error', function(e) {
			    console.error('Action:', e.action);
			    console.error('Trigger:', e.trigger);
			});
		

		}

		if ($('.layout-post').length) {
		    codePreprocessing();
		    codeCopy();
		} 
	</script>





<link rel="stylesheet" href="../../../../plugin/fancybox/jquery.fancybox.css">
<script src="../../../../plugin/fancybox/jquery.fancybox.js"></script>

<script type="text/javascript">
	var titleID = $('.article-title a'),
		imageID = $('.article-content img'),
		videoID = $('.article-content video');

	var postTitle = titleID.text() ? titleID.text() : "No post title!";

	imageID.each(function() {
		var imgPath = $(this).attr('src'),
			imgTitle = $(this).attr('alt') ? $(this).attr('alt') : "No image description!";

		//给每个匹配的<img>元素打包, 即添加父元素<a>
		$(this).wrap('<a data-fancybox="gallery" data-caption=" 《 ' + postTitle + ' 》 ' + imgTitle + ' "  href=" ' + imgPath + ' "> </a>');
	});

	videoID.each(function() {
		var videoPath = $(this).attr('src');

		//给每个匹配的<img>元素打包, 即添加父元素<a>
		$(this).wrap('<a data-fancybox href=" ' + videoPath + ' "> </a>');
	});
	//TODO：支持html5 video

	if($('#layout-post').length) {
		$('[data-fancybox="gallery"]').fancybox({
			loop: true,
			buttons: [
				"zoom",
				"share",
				"slideShow",
				"fullScreen",
				//"download",
				"thumbs",
				"close"
			],
			protect: false
		});
	}
</script>
		</main>

		<!--footer-->
		<footer>
	<div class="social">
		<ul>
	
		<li>
			<a href="http://github.com/addyzk" target="_blank">
				<i class="fa fa-github"></i>
			</a>
		</li>
	
		<li>
			<a href="https://weibo.com/u/2943549915" target="_blank">
				<i class="fa fa-weibo"></i>
			</a>
		</li>
	
		<li>
			<a href="http://github.com/" target="_blank">
				<i class="fa fa-pinterest"></i>
			</a>
		</li>
	
		<li>
			<a href="http://github.com/" target="_blank">
				<i class="fa fa-instagram"></i>
			</a>
		</li>
	
		<li>
			<a href="http://github.com/" target="_blank">
				<i class="fa fa-twitter"></i>
			</a>
		</li>
	
		<li>
			<a href="../../../../atom.xml" target="_blank">
				<i class="fa fa-rss"></i>
			</a>
		</li>
			
</ul>

	</div>
		
	<div class="copyright">
		<p>
			 
				&copy;2016 - 2019, content by SorrisoYi. All Rights Reserved.
			
			
			

	<!-- busuanzi -->
	<!-- busuanzi -->

		
	<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
	

		<span id="busuanzi_container_page_pv">
	  		本文总阅读量<span id="busuanzi_value_page_pv"></span>次
		</span>

	




		</p>
		<p>
			<a href="http://hexo.io/" title="Hexo" target="_blank" rel="noopener">Hexo</a> Theme <a href="https://github.com/Sariay/hexo-theme-Annie" title="Annie" target="_blank" rel="noopener">Annie</a> by Sariay.
			<a href="javascript:zh_tran('s');" class="zh_click" id="zh_click_s">简体</a> 
			<a href="javascript:zh_tran('t');" class="zh_click" id="zh_click_t">繁體</a>				
		</p>
	</div>		
</footer>
		
	<!-- set '1' to show motto in all pages! -->

	<script src="../../../../plugin/motto/motto.js"></script>
	
	<script type="text/javascript">
		$(".motto").html( getMingYanContent() );
	</script>	




	<!--
	时间：2018-10-3
	描述：
		插件名称：hexo-generator-search-zip
		插件来源: https://github.com/SuperKieran/hexo-generator-search-zip
		代码参考：https://github.com/SuperKieran/TKL/blob/master/layout/_partial/search.ejs(Include: js & css)	
-->
<div class="popup search-popup local-search-popup">
	<div class="local-search-container">
		<span class="popup-btn-close">
      		ESC
   		</span>
		<div class="local-search-header">
			<div class="input-prompt">				
			</div>
			<input autocomplete="off" placeholder="Search..." type="text" id="local-search-input">
		</div>
		<div class="local-search-body">
			<div id="local-search-output"></div>
		</div>
		<div class="local-search-footer">
			<div class="topN-post">				
				

   
	<div id="topN">
		<div class="topN-title" data-title="热门文章"></div> 
	</div>
	
    <script>
        var appid = 'bdanoXQN5zgeVyspG5e9RF7o-gzGzoHsz',
            appkey = 'RVVmwDan5FhjuwkKxenjOGSE',
            limitCount = 10;
        if( $('#topN').length ){
            AV.initialize(appid, appkey);
            var Counter = AV.Object.extend("Counter");  
            topNPost(Counter, limitCount);
        }
    </script>
   
								
			</div>
		</div>
	</div>
</div>

<script src="../../../../plugin/search/ziploader.js"></script>
<script src="../../../../plugin/search/search.js"></script>

<script type="text/javascript">
	var search_path = 'search.json',
		zip_Path = '/SorrisiYi/search.zip',
		version_Path = '/SorrisiYi/searchVersion.txt',
		input_Trigger = 'auto',
		top_N = '2';

	themeLocalSearch({
		search_path, 
		zip_Path, 
		version_Path, 
		input_Trigger, 
		top_N
	});
</script>


<!-- love effect -->

	<script src="../../../../plugin/love/love.js"></script>


<!-- back to top -->

	
	<div id="totop">
  		<a href="javascript:;" name="TOTOP" class="fa fa-arrow-up"></a>
	</div>




<!-- site analysis -->


	<!-- site-analysis -->
	
	
	
	
	
 

<script src="../../../../plugin/vibrant/vibrant.js"></script>
<script src="../../../../plugin/chinese/chinese.js"></script>
<script src="../../../../plugin/imgLazyLoader/yall.min.js"></script>
<script src="../../../../plugin/imgResize/jquery.resizeimagetoparent.min.js"></script>
<script src="../../../../plugin/nicescroll/jquery.nicescroll.js"></script>
<script src="../../../../js/resizediv.js"></script>
<script src="../../../../js/main.js"></script>
	</body>	
</html>